﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;

namespace Library
{
    public class Execute
    {
        private SqlConnection conn;

        public string getConnectionString()
        {
            return ""; //CRMConnectionString.StrConn.ToString();
        }
        /// <summary>
        /// Execute a store procedure return a instance of SqlDataReader
        /// </summary>
        /// <param name="query">Store Procedure Name</param>
        /// <param name="querypara">Param List</param>
        /// <returns>A instance of SqlDataReader contain result of stored procedure</returns>
        public List<T> GetList<T>(string query, params SqlParameter[] querypara)
        {
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            var m_List = new List<T>();
            try
            {
                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand();
                cmd.CommandTimeout = 180;
                cmd.Connection = conn;
                cmd.CommandText = query;
                cmd.CommandType = CommandType.StoredProcedure;
                if (querypara != null)
                    for (int i = 0; i < querypara.Length; i++)
                        cmd.Parameters.Add(querypara[i]);
                dr = cmd.ExecuteReader();
                if (dr == null || dr.FieldCount == 0)
                    return null;
                int fCount = dr.FieldCount;
                Type m_Type = typeof(T);
                PropertyInfo[] l_Property = m_Type.GetProperties();
                object obj;

                string pName;
                while (dr.Read())
                {
                    obj = Activator.CreateInstance(m_Type);
                    for (int i = 0; i < fCount; i++)
                    {
                        pName = dr.GetName(i);
                        if (dr[i] != DBNull.Value && l_Property.Where(a => a.Name == pName).Select(a => a.Name).Count() > 0)
                        {
                            m_Type.GetProperty(pName).SetValue(obj, dr[i], null);
                        }
                    }
                    m_List.Add((T)obj);
                }
                dr.Close();
                conn.Close();

            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            { conn.Dispose(); }
            return m_List;
        }
        public List<T> GetList<T>(string query)
        {
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            var m_List = new List<T>();
            try
            {
                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand();
                cmd.CommandTimeout = 180;
                cmd.Connection = conn;
                cmd.CommandText = query;
                cmd.CommandType = CommandType.Text;
                dr = cmd.ExecuteReader();
                if (dr == null || dr.FieldCount == 0)
                    return null;
                int fCount = dr.FieldCount;
                Type m_Type = typeof(T);
                PropertyInfo[] l_Property = m_Type.GetProperties();
                object obj;

                string pName;
                while (dr.Read())
                {
                    obj = Activator.CreateInstance(m_Type);
                    for (int i = 0; i < fCount; i++)
                    {
                        pName = dr.GetName(i);
                        if (dr[i] != DBNull.Value && l_Property.Where(a => a.Name == pName).Select(a => a.Name).Count() > 0)
                        {
                            m_Type.GetProperty(pName).SetValue(obj, dr[i], null);
                        }
                    }
                    m_List.Add((T)obj);
                }
                dr.Close();
                conn.Close();

            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            { conn.Dispose(); }
            return m_List;
        }
        /// <summary>
        /// Execute a store procedure return all records  to influence
        /// </summary>
        /// <param name="query">Stored procedure name</param>
        /// <param name="querypara">Param List</param>
        /// <returns>return all records  to be affected after executing the stored procedure</returns>
        public int executeNonQuery(string query, params SqlParameter[] querypara)
        {
            SqlCommand cmd = null;
            int affectedRows = 0;
            try
            {

                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = query;
                cmd.CommandType = CommandType.StoredProcedure;
                if (querypara != null)
                    for (int i = 0; i < querypara.Length; i++)
                        cmd.Parameters.Add(querypara[i]);
                affectedRows = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (NullReferenceException ex)
            {
                throw;
            }
            finally
            { conn.Dispose(); }
            return affectedRows;
        }
        public int executeNonQuery(string query)
        {
            SqlCommand cmd = null;
            int affectedRows = 0;
            try
            {

                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = query;
                cmd.CommandType = CommandType.Text;
                affectedRows = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (NullReferenceException ex)
            {
                throw;
            }
            finally
            { conn.Dispose(); }
            return affectedRows;
        }
        /// <summary>
        /// Execute a store procedure return first row and first column of all records
        /// </summary>
        /// <param name="query">Stored procedure name</param>
        /// <param name="querypara">Param List</param>
        /// <returns>return first row and first column of a stored procedure is boxing</returns>
        public T GetObject<T>(string query, params SqlParameter[] querypara)
        {
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            //var m_List = new List<T>();
            // T obj;
            try
            {
                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = query;
                cmd.CommandType = CommandType.StoredProcedure;
                if (querypara != null)
                    for (int i = 0; i < querypara.Length; i++)
                        cmd.Parameters.Add(querypara[i]);
                dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
                if (dr == null || dr.FieldCount == 0)
                    return default(T);
                int fCount = dr.FieldCount;
                Type m_Type = typeof(T);
                PropertyInfo[] l_Property = m_Type.GetProperties();

                string pName;
                if (dr.Read())
                {
                    object obj = Activator.CreateInstance(m_Type);
                    for (int i = 0; i < fCount; i++)
                    {
                        pName = dr.GetName(i);
                        if (dr[i] != DBNull.Value && l_Property.Where(a => a.Name == pName).Select(a => a.Name).Count() > 0)
                        {
                            m_Type.GetProperty(pName).SetValue(obj, dr[i], null);
                        }
                    }
                    return (T)obj;
                    //m_List.Add((T)obj);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            { conn.Dispose(); }
            return default(T);
        }
        public T GetObject<T>(string query)
        {
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            //var m_List = new List<T>();
            // T obj;
            try
            {
                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = query;
                cmd.CommandType = CommandType.Text;
                dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
                if (dr == null || dr.FieldCount == 0)
                    return default(T);
                int fCount = dr.FieldCount;
                Type m_Type = typeof(T);
                PropertyInfo[] l_Property = m_Type.GetProperties();

                string pName;
                if (dr.Read())
                {
                    object obj = Activator.CreateInstance(m_Type);
                    for (int i = 0; i < fCount; i++)
                    {
                        pName = dr.GetName(i);
                        if (dr[i] != DBNull.Value && l_Property.Where(a => a.Name == pName).Select(a => a.Name).Count() > 0)
                        {
                            m_Type.GetProperty(pName).SetValue(obj, dr[i], null);
                        }
                    }
                    return (T)obj;
                    //m_List.Add((T)obj);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            { conn.Dispose(); }
            return default(T);
        }
        /// <summary>
        /// Execute a store procedure return first row and first column of all records
        /// </summary>
        /// <param name="query">Stored procedure name</param>
        /// <param name="querypara">Param List</param>
        /// <returns>return first row and first column of a stored procedure is boxing</returns>
        public object executeScalar(string query, params SqlParameter[] querypara)
        {
            SqlCommand cmd = null;
            object value = null;
            try
            {
                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = query;
                cmd.CommandType = CommandType.StoredProcedure;
                if (querypara != null)
                    for (int i = 0; i < querypara.Length; i++)
                        cmd.Parameters.Add(querypara[i]);
                value = cmd.ExecuteScalar();
                conn.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Dispose();
            }
            return value;
        }
        public object executeScalar(string query)
        {
            SqlCommand cmd = null;
            object value = null;
            try
            {
                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = query;
                cmd.CommandType = CommandType.Text;
                value = cmd.ExecuteScalar();
                conn.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Dispose();
            }
            return value;
        }
        /// <summary>
        /// Execute procedure with DataSet type
        /// </summary>
        /// <param name="query">Stored procedure name</param>
        /// <param name="querypara">Param List</param>
        /// <returns>A instance of DataSet</returns>
        public DataSet executeDataSet(string query, params SqlParameter[] querypara)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = null;
            SqlCommand cmd = null;
            try
            {
                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand(query, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                if (querypara != null)
                    for (int i = 0; i < querypara.Length; i++)
                        cmd.Parameters.Add(querypara[i]);
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
                conn.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            { conn.Dispose(); }
            return ds;
        }
        public DataSet executeDataSet(string query)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = null;
            SqlCommand cmd = null;
            try
            {
                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand(query, conn);
                cmd.CommandType = CommandType.Text;
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
                conn.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            { conn.Dispose(); }
            return ds;
        }
        /// <summary>
        /// execute DataTable
        /// </summary>
        /// <param name="query"></param>
        /// <param name="querypara"></param>
        /// <returns></returns>
        public DataTable executeDataTable(string query, params SqlParameter[] querypara)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter adapter = null;
            SqlCommand cmd = null;
            try
            {
                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand(query, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                if (querypara != null)
                    for (int i = 0; i < querypara.Length; i++)
                        cmd.Parameters.Add(querypara[i]);
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dt);
                conn.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            { conn.Dispose(); }
            return dt;
        }
        public DataTable executeDataTable(string query)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter adapter = null;
            SqlCommand cmd = null;
            try
            {
                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand(query, conn);
                cmd.CommandType = CommandType.Text;
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dt);
                conn.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            { conn.Dispose(); }
            return dt;
        }
    }
}
